/**
 * 
 */
package db;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import parse.RowVO;

/**
 * <p>Title:DBUtils</p>
 * <p>Description:TODO</p>
 * @author	xiongf
 * @date	2016年1月26日 上午10:07:13
 * @version 1.0
 */
public class DBUtils {

	private static String dbUser;

	private static String dbPass;

	private static String dbUrl;

	private static String dbDriver;

	public static String getDbUser() {
		return dbUser;
	}

	public static void setDbUser(String dbUser) {
		DBUtils.dbUser = dbUser;
	}

	public static String getDbPass() {
		return dbPass;
	}

	public static void setDbPass(String dbPass) {
		DBUtils.dbPass = dbPass;
	}

	public static String getDbUrl() {
		return dbUrl;
	}

	public static void setDbUrl(String dbUrl) {
		DBUtils.dbUrl = dbUrl;
	}

	public static String getDbDriver() {
		return dbDriver;
	}

	public static void setDbDriver(String dbDriver) {
		DBUtils.dbDriver = dbDriver;
	}

	static {
		Properties prop = new Properties();
		try {
			prop.load(DBUtils.class.getClassLoader().getResourceAsStream("db.properties"));
			setDbUser(prop.getProperty("db.user"));
			setDbPass(prop.getProperty("db.passsword"));
			setDbUrl(prop.getProperty("db.url"));
			setDbDriver(prop.getProperty("db.driverClass"));
			
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	private static Connection getConnection() throws Exception {
		Connection conn = null;
		try {
			Class.forName(dbDriver);
			conn = DriverManager.getConnection(dbUrl, dbUser, dbPass);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		return conn;
	}

	// 数据插入
	public static void addSign(RowVO vo) throws Exception {
		Connection conn = null;
		try {
			conn = getConnection();
			String sql = "insert into RELE_TD_CUSM_SIGN(cust_no, acct_no) values (?, ?)";
			QueryRunner qr = new QueryRunner();
			qr.update(conn, sql, vo.getCustno(), vo.getAcctno());
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DbUtils.close(conn);
		}
	}

	// 数据表删除
	public static void delSign() throws Exception {
		Connection conn = null;
		try {
			conn = getConnection();
			String sql = "delete from RELE_TD_CUSM_SIGN";
			QueryRunner qr = new QueryRunner();
			qr.update(conn, sql);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DbUtils.close(conn);
		}
	}

	// 数据获取
	public static List<RowVO> getSign() throws Exception {
		Connection conn = null;
		try {
			conn = getConnection();
			String sql = "select cust_no as custno,acct_no as acctno from RELE_TD_CUSM_SIGN";
			QueryRunner qr = new QueryRunner();

			List<RowVO> topicList = new ArrayList<RowVO>();
			topicList = qr.query(conn, sql, new BeanListHandler<RowVO>(RowVO.class));
			return topicList;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DbUtils.close(conn);
		}
	}

	public static void main(String[] args) throws Exception {
		List<RowVO> topicList = getSign();
		System.out.println(topicList);
	}

}
